﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;
using ICSharpCode.TextEditor.Document;

namespace CEQuery
{
    public partial class QueryPaneControl : UserControl
    {
        public string ConnectionString { get; set; }
        public string TableSelectQuery { get; set; }
        public string TableInsertQuery { get; set; }
        public bool UpdatablePane { get; set; }

        private SqlCeConnection connection;
        private SqlCeCommand cmdTable;
        private SqlCeDataAdapter daTable;
        private DataSet dsResultTable;

        public event StatusEventHandler StatusEvent;
        public event RefreshRequestEventHandler RefreshRequestEvent;
        protected virtual void OnStatusEvent(StatusEventArgs e)
        {
            StatusEvent(this, e);
        }
        protected virtual void OnRefreshRequestEvent(EventArgs e)
        {
            RefreshRequestEvent(this, e);
        }

        public QueryPaneControl()
        {
            InitializeComponent();
            UpdatablePane = false;
        }

        private void QueryPaneControl_Load(object sender, EventArgs e)
        {
            string strPathXSHD = AppDomain.CurrentDomain.BaseDirectory + "EditorResources\\";
            FileSyntaxModeProvider provider = new FileSyntaxModeProvider(strPathXSHD);
            HighlightingManager manager = HighlightingManager.Manager;
            manager.AddSyntaxModeFileProvider(provider);
            rtQuery.Document.HighlightingStrategy = manager.FindHighlighter("CESQL");

            rtQuery.Font = new Font("Trebuchet MS", 10, FontStyle.Regular);
            rtQuery.ShowEOLMarkers = false;
            rtQuery.ShowInvalidLines = false;
            rtQuery.ShowSpaces = false;
            rtQuery.ShowTabs = false;
            rtQuery.ShowVRuler = false;
            rtQuery.ShowHRuler = false;
            rtQuery.IsIconBarVisible = false;
            rtQuery.ShowLineNumbers = false;
            rtQuery.AutoScroll = true;

            rtQuery.ActiveTextAreaControl.AllowDrop = true;

            connection = new SqlCeConnection(ConnectionString);
            if (!string.IsNullOrEmpty(this.TableInsertQuery))
            {
                rtQuery.Tag = "ProcessAll";
                rtQuery.Text = this.TableInsertQuery;
            }
            if (!string.IsNullOrEmpty(this.TableSelectQuery))
            {
                rtQuery.Text = this.TableSelectQuery;
                rtQuery.Enabled = false;
                PrepareManipulationBed();
            }

            this.rtQuery.DragDrop += new DragEventHandler(rtQuery_DragDrop);

            this.rtQuery.ActiveTextAreaControl.TextArea.DragDrop += new DragEventHandler(rtQuery_DragDrop);
            this.rtQuery.ActiveTextAreaControl.TextArea.DragEnter += new DragEventHandler(rtQuery_DragEnter);
            this.rtQuery.ActiveTextAreaControl.TextArea.KeyDown += new KeyEventHandler(rtQuery_KeyDown);

        }

        void rtQuery_DragEnter(object sender, DragEventArgs e)
        {
            e.Effect = DragDropEffects.Copy;
        }
        private void rtQuery_DragDrop(object sender, DragEventArgs e)
        {
            rtQuery.ActiveTextAreaControl.TextArea.InsertString("[" + ((TreeNode)e.Data.GetData(typeof(TreeNode))).Tag.ToString() + "]");
        }
        private void rtQuery_KeyPress(object sender, KeyPressEventArgs e)
        {
            //if (Convert.ToInt32(e.KeyChar) == 22)
            //{
            //    rtQuery.Tag = "ProcessAll";
            //}

        }
        private void rtQuery_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                RunQuery();
            }
        }
        private void updateToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                daTable.Update(dsResultTable);
                PrepareManipulationBed();
                MessageBox.Show("Table has been updated successfully.");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException != null ? ex.InnerException.Message : ex.Message);
            }
        }
        private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (dgvQuery.SelectedRows.Count > 0)
                {
                    foreach (DataGridViewRow row in dgvQuery.SelectedRows)
                    {
                        if (!row.IsNewRow)
                            dgvQuery.Rows.Remove(row);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException != null ? ex.InnerException.Message : ex.Message);
            }
        }
        private void dgvQuery_MouseUp(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (dgvQuery.RowCount > 0)
                {
                    ctxGridMenu.Visible = true;
                    if (UpdatablePane)
                    {
                        ctxGridMenu.Items[0].Enabled = true;
                        ctxGridMenu.Items[1].Enabled = true;
                    }
                    else
                    {
                        ctxGridMenu.Items[0].Enabled = false;
                        ctxGridMenu.Items[1].Enabled = false;
                    }
                }
                else
                {
                    ctxGridMenu.Visible = false;
                }
            }
        }
        private void dgvQuery_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            MessageBox.Show(e.Exception.InnerException != null ? e.Exception.InnerException.Message : e.Exception.Message);
        }

        public void RunQuery()
        {
            try
            {
                if (!rtQuery.Enabled)
                {
                    return;
                }
                if (ConnectionString != string.Empty)
                {
                    SqlCeCommand cmd = new SqlCeCommand();
                    cmd.Connection = connection;
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }
                    if (rtQuery.ActiveTextAreaControl.SelectionManager.SelectedText.Length > 0)
                        cmd.CommandText = rtQuery.ActiveTextAreaControl.SelectionManager.SelectedText;
                    else
                        cmd.CommandText = rtQuery.Text.Trim();

                    if (cmd.CommandText.Trim().ToUpper().StartsWith("INSERT") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("UPDATE") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("DELETE") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("CREATE") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("ALTER") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("DROP") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("SET") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("DBCC"))
                    {
                        int rowsEffected = cmd.ExecuteNonQuery();
                        MessageBox.Show("Query has been executed successfully.");
                        StatusEventArgs statusArgs = new StatusEventArgs();
                        statusArgs.Message = string.Format("{0} Records effected.", rowsEffected.ToString());
                        OnStatusEvent(statusArgs);
                        OnRefreshRequestEvent(new EventArgs());
                    }
                    else
                    {
                        dgvQuery.DataSource = null;
                        SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
                        DataSet dsResult = new DataSet();
                        da.Fill(dsResult);
                        dgvQuery.DataSource = dsResult.Tables[0];
                        dgvQuery.ReadOnly = true;

                        for (int counter = dgvQuery.Columns.Count - 1; counter >= 0; counter--)
                        {
                            if (dgvQuery.Columns[counter].CellType == typeof(DataGridViewImageCell))
                            {
                                DataGridViewTextBoxColumn colAddl = new DataGridViewTextBoxColumn();
                                colAddl.HeaderText = dgvQuery.Columns[counter].HeaderText;
                                colAddl.DataPropertyName = dgvQuery.Columns[counter].DataPropertyName;
                                dgvQuery.Columns.RemoveAt(counter);
                                dgvQuery.Columns.Insert(counter, colAddl);
                            }
                        }
                        StatusEventArgs statusArgs = new StatusEventArgs();
                        statusArgs.Message = string.Format("{0} Records found.", dsResult.Tables[0].Rows.Count.ToString());
                        OnStatusEvent(statusArgs);
                    }
                    connection.Close();
                }
                else
                {
                    MessageBox.Show("Select a DB first");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public bool SaveQuery()
        {
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Title = "Save Query";
                saveFileDialog.Filter = "SQL Files (*.sql)|*.sql";
                saveFileDialog.RestoreDirectory = true;
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    File.WriteAllText(saveFileDialog.FileName, rtQuery.Text.Replace("\n", Environment.NewLine));
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        private void PrepareManipulationBed()
        {
            cmdTable = new SqlCeCommand();
            cmdTable.Connection = connection;
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            cmdTable.CommandText = this.TableSelectQuery;

            daTable = new SqlCeDataAdapter(cmdTable);
            dsResultTable = new DataSet();
            daTable.Fill(dsResultTable);
            dgvQuery.DataSource = dsResultTable.Tables[0];

            for (int counter = dgvQuery.Columns.Count - 1; counter >= 0; counter--)
            {
                if (dgvQuery.Columns[counter].CellType == typeof(DataGridViewImageCell))
                {
                    DataGridViewTextBoxColumn colAddl = new DataGridViewTextBoxColumn();
                    colAddl.HeaderText = dgvQuery.Columns[counter].HeaderText;
                    colAddl.DataPropertyName = dgvQuery.Columns[counter].DataPropertyName;
                    dgvQuery.Columns.RemoveAt(counter);
                    dgvQuery.Columns.Insert(counter, colAddl);
                }
            }
            dgvQuery.AllowUserToAddRows = true;
            dgvQuery.AllowUserToDeleteRows = true;
            dgvQuery.ReadOnly = false;

            SqlCeCommandBuilder cmdbuilder = new SqlCeCommandBuilder(daTable);

            StatusEventArgs statusArgs = new StatusEventArgs();
            statusArgs.Message = string.Format("{0} Records found.", dsResultTable.Tables[0].Rows.Count.ToString());
            OnStatusEvent(statusArgs);
        }

        public void exportToExcel(DataSet source, string fileName)
        {

            System.IO.StreamWriter excelDoc;

            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<?xml version=\"1.0\"?>\r\n<?mso-application progid=\"Excel.Sheet\"?>\r\n<Workbook " +
                  "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
                  " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
                  "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
                  "office:spreadsheet\">\r\n <Styles>\r\n " +
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
                  "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
                  "\r\n <Protection/>\r\n </Style>\r\n " +
                  "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
                  "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
                  "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
                  " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
                  "ss:ID=\"Decimal\">\r\n <NumberFormat " +
                  "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
                  "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
                  "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
                  "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
                  "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
                  "</Styles>\r\n ";
            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 1;

            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");
            for (int index = 0; index < source.Tables[0].Columns.Count; index++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Tables[0].Columns[index].ColumnName);
                excelDoc.Write("</Data></Cell>");
            }
            excelDoc.Write("</Row>");
            foreach (DataRow row in source.Tables[0].Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "
                for (int col = 0; col < source.Tables[0].Columns.Count; col++)
                {
                    System.Type rowType;
                    rowType = row[col].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = row[col].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            DateTime XMLDate = (DateTime)row[col];
                            string XMLDatetoString = ""; //Excel Converted Date
                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(row[col].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(row[col].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int32":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(row[col].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int64":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(row[col].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        //case "System.Byte":
                        //    excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                        //            "<Data ss:Type=\"Number\">");
                        //    excelDoc.Write(row[col].ToString());
                        //    excelDoc.Write("</Data></Cell>");
                        //    break;
                        case "System.Decimal":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(row[col].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(row[col].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                 "<Data ss:Type=\"String\">");
                            excelDoc.Write(row[col].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }
        private void exportToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (dgvQuery.DataSource != null)
                {
                    SaveFileDialog saveFileDialog = new SaveFileDialog();
                    saveFileDialog.Title = "Save Result";
                    saveFileDialog.Filter = "Excel XML Spreadsheet (*.xml)|*.xml";
                    saveFileDialog.RestoreDirectory = true;
                    if (saveFileDialog.ShowDialog() == DialogResult.OK)
                    {
                        DataSet ds = new DataSet();
                        ds.Tables.Add(((DataTable)dgvQuery.DataSource).Copy());
                        exportToExcel(ds, saveFileDialog.FileName);
                        MessageBox.Show("Export is done successfully.");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


    }

    public delegate void StatusEventHandler(object sender, StatusEventArgs e);
    public delegate void RefreshRequestEventHandler(object sender, EventArgs e);
    public class StatusEventArgs : EventArgs
    {
        public string Message { get; set; }
    }
}
